﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model.DocModel.APIResultModel;
using HCQ2_Model.DocModel;
using System.Data;
using HCQ2_Model.SelectModel;
using HCQ2_Model.WeiXinApiModel.ResultApiModel;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRCustomPassDAL: IT_VRCustomPassDAL
    {
        /// <summary>
        ///  获取关卡列表
        /// </summary>
        /// <param name="model">参数</param>
        /// <param name="total">数量</param>
        /// <returns></returns>
        public List<HeadPassModel> GetTableData(HeadTableParamModel model, out int total)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM 
            (SELECT pass.*,ROW_NUMBER() OVER(ORDER BY pass.cp_id) AS num,wordCount=(select count(0) from T_VRCustomPassWord word where word.cp_id=pass.cp_id AND word.file_id=pass.file_id) FROM 
            (SELECT file_id,folder_id,folder_path FROM dbo.T_DocumentFolderRelation WHERE folder_id={1}) rel INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0)info ON rel.file_id=info.file_id INNER JOIN
            (SELECT cp_id,file_id,file_name,cp_name,cp_code,create_name,update_name,cp_note,CONVERT(varchar(100),create_date,120) as create_date,CONVERT(varchar(100),update_date,120) as update_date FROM T_VRCustomPass WHERE 1=1 ", model.rows,model.folder_id);
            //名称
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND (file_name LIKE '{0}%' OR cp_name LIKE '{0}%' OR file_code='{0}') ", model.keyword);
            //课件id
            if (model.file_id > 0)
                sb.AppendFormat(" AND file_id={0} ", model.file_id);
            sb.AppendFormat(")pass ON rel.file_id=pass.file_id)pass WHERE pass.num>{0};", (model.page - 1) * model.rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            //数量
            sb?.Clear();
            sb.AppendFormat(@"SELECT COUNT(0) AS num FROM 
            (SELECT file_id,folder_id,folder_path FROM dbo.T_DocumentFolderRelation WHERE folder_id={0}) rel INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE if_remove=0)info ON rel.file_id=info.file_id INNER JOIN
            (SELECT * FROM T_VRCustomPass WHERE 1=1 ", model.folder_id);
            if (!string.IsNullOrEmpty(model.keyword))
                sb.AppendFormat(" AND file_name LIKE '{0}%' OR cp_name LIKE '{0}%' ", model.keyword);
            if (model.file_id > 0)
                sb.AppendFormat(" AND file_id={0} ", model.file_id);
            sb.AppendFormat(")pass ON rel.file_id=pass.file_id;");
            total= HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<HeadPassModel>(dt);
        }
        /// <summary>
        ///  获取课件集合：特指头记
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public List<SelectModel> GetClassData(int folder_id,bool isOk=false, bool notStatus = false)
        {
            string str = "", info = "";
            if (folder_id > 0)
                str = string.Format(" WHERE folder_id={0} ",folder_id);
            if (isOk)
                info += " AND file_status=5 ";
            if (notStatus)
                info += " AND file_status<>6 ";
            sb?.Clear();
            sb.Append($@"SELECT CAST(info.file_id AS NVARCHAR(10)) AS value,CASE WHEN info.file_status=4 THEN info.file_name+'【已下架】' ELSE info.file_name END AS text FROM  
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_sys=1 and folder_path LIKE 
            (SELECT folder_path FROM dbo.T_DocumentFolder WHERE folder_id={folder_id})+'%')folder INNER JOIN 
            (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation {str})rel ON rel.folder_id = folder.folder_id INNER JOIN
            (SELECT file_id,file_name,file_status FROM dbo.T_DocumentInfo WHERE if_remove=0 {info})info ON rel.file_id=info.file_id ORDER BY info.file_status DESC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<SelectModel>(dt);
        }
        /// <summary>
        ///  获取用户课件列表
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="fileId">课件id</param>
        /// <returns></returns>
        public List<VRCustomPassRecord> GetCustomList(int userId, int fileId)
        {
            sb?.Clear();
            sb.Append($@"SELECT CONVERT(int,ROW_NUMBER() OVER(ORDER BY cp_order,cp_id)) AS rowNumber,pass.*,ISNULL(score,0)AS score,is_game,CONVERT(varchar(100),game_start,20) AS game_start,CONVERT(varchar(100),game_end,20) AS game_end FROM 
            (SELECT * FROM dbo.T_VRCustomPass WHERE file_id={fileId})pass LEFT JOIN
            (SELECT MAX(score) AS score,file_id,folder_code FROM dbo.T_VRExperienceRecord WHERE user_id={userId}
             GROUP BY file_id,folder_code)record ON pass.file_id = record.file_id AND pass.cp_code = record.folder_code INNER JOIN
             (SELECT file_id,is_game,game_start,game_end FROM dbo.T_DocumentInfo)info ON info.file_id = pass.file_id;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<VRCustomPassRecord>(dt);
        }
    }
}
